package com.easyattendance.servlets;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.easyattendance.model.Class;
import com.easyattendance.model.Parent;
import com.easyattendance.model.Section;
import com.easyattendance.model.Student;
import com.easyattendance.model.Subject;
import com.easyattendance.model.Teacher;
import com.easyattendance.model.TimeTable;
import com.easyattendance.services.AddAllTimeSlotsService;
import com.easyattendance.services.AddClassService;
import com.easyattendance.services.AddParentService;
import com.easyattendance.services.AddSectionService;
import com.easyattendance.services.AddStudentService;
import com.easyattendance.services.AddSubjectService;
import com.easyattendance.services.AddTeacherService;
import com.easyattendance.services.TimeTableService;

@WebServlet("/ReadStudentExcel")
public class ReadStudentExcel extends HttpServlet{
	
	protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

	AddTeacherService teacherService = new AddTeacherService();
	AddClassService classService = new AddClassService();
	
	String currentUser = (String) request.getSession().getAttribute("currentUser");
	String classId=classService.getClassIdByTeacherId(currentUser);
	String sectionEmailId = teacherService.getSectionEmailIdByTeacherId(currentUser);
	String sectionId = teacherService.getSectionIdByTeacherId(currentUser);
	int flag=0,flag1=0;
	
	//Blank workbook
	XSSFWorkbook workbook1 = new XSSFWorkbook();
	XSSFWorkbook workbook2 = new XSSFWorkbook();
	
	//Create a blank sheet with days and timeslot headers...
	XSSFSheet sheet1 = workbook1.createSheet("Parent");
	XSSFSheet sheet2 = workbook2.createSheet("Student");
	
	int rownum = 0;
	int cellnum = 0;
	Row row1 = sheet1.createRow(rownum++);
	Cell cell1= row1.createCell(cellnum++);
	rownum = 0;
	cellnum = 0;
	Row row2 = sheet2.createRow(rownum++);
	Cell cell2= row2.createCell(cellnum++);

		
		// ----------------------------------------DONOT DELETE THIS-----------------------------------------------------------------
		/*
		 * Read Students inputed by class teacher in our generated excel sheet
		 * 
		 * 
		 */
		
		try
		{
			FileInputStream file1 = new FileInputStream(new File("F:\\parentlist.xlsx"));
			
			String[] cellValues = new String[5];
			double[] cellValues1 = new double[3];
			String delimiter = ",";
			String parent_id=null;
			String parent_name=null;
			String parent_email_id=null;
			Long parent_mob_no = 0L;
			String parent_job=null;
			Long parent_home_no=0L;
			Long parent_office_no=0L;
			String parent_gender=null;
			
			//Create Workbook instance holding reference to .xlsx file
			workbook1 = new XSSFWorkbook(file1);

			//Get first/desired sheet from the workbook
			sheet1 = workbook1.getSheetAt(0);

			//Iterate through each rows one by one
			Iterator<Row> rowIterator = sheet1.iterator();

			rowIterator.next();
			
			while (rowIterator.hasNext()) 
			{
				row1 = rowIterator.next();
				//For each row, iterate through all the columns
				Iterator<Cell> cellIterator = row1.cellIterator();
				
				for (int i=0; cellIterator.hasNext(); i++) 
				{
					cell1 = cellIterator.next();
					
					  if(i>=0 && i<5)
						  cellValues[i] = cell1.getStringCellValue();
					  else if(i>=5 && i<=7)
						  cellValues1[i-5]= cell1.getNumericCellValue();		
				}
			
					parent_id=cellValues[0];
					parent_name=cellValues[1];
					parent_job=cellValues[2];
					parent_email_id=cellValues[3];
					if(cellValues[4].equalsIgnoreCase("Male"))
						parent_gender="M";
					else
						parent_gender="F";
					
					parent_home_no=(long)cellValues1[0];
					parent_mob_no=(long)cellValues1[1];
					parent_office_no=(long) cellValues1[2];
					
					boolean result=addParentEntry(parent_id,parent_name,parent_email_id,parent_mob_no,parent_home_no,parent_job,parent_office_no,parent_gender);
					
					if(!result)
						flag++;
					//out.close();
				
			
			}
			file1.close();
			System.out.println("Parent data extracted from excel");
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		try
		{
			
			FileInputStream file2 = new FileInputStream(new File("F:\\studentlist.xlsx"));

			String stud_id = null;
			int stud_roll_no = 0;
			String[] cellValues = new String[8];
			double cellValues1[]=new double[3];
			Date cellDateValue=null;
			String delimiter = ",";
			String stud_address=null;
			String stud_email_id=null;
			String stud_fname=null;
			String stud_lname=null;
			Long stud_home_no=0L;
			Long stud_mobile_no=0L;
			String parent_id=null;
			Date stud_dob=null;
			String stud_gender=null;
			
			//Create Workbook instance holding reference to .xlsx file
			workbook2 = new XSSFWorkbook(file2);

			//Get first/desired sheet from the workbook
			sheet2 = workbook2.getSheetAt(0);

			//Iterate through each rows one by one
			Iterator<Row> rowIterator= sheet2.iterator();
			
		
			rowIterator.next();
			
			while (rowIterator.hasNext()) 
			{
				row2 = rowIterator.next();
				//For each row, iterate through all the columns
				Iterator<Cell> cellIterator = row2.cellIterator();
				
				for (int i=0; cellIterator.hasNext(); i++) 
				{
					cell2 = cellIterator.next();
					
					  if(i>=1 && i<=7)
						  cellValues[i-1] = cell2.getStringCellValue();
					  else if(i==0)
						  cellValues1[i]= cell2.getNumericCellValue();
					  else if(i==8)
						  cellDateValue=cell2.getDateCellValue();
					  else if(i==9 || i==10)
						  cellValues1[i-8]= cell2.getNumericCellValue();
				}
				
							stud_roll_no =(int)cellValues1[0];
							stud_address=cellValues[0];
							stud_email_id=cellValues[1];
							stud_fname=cellValues[2];
							stud_lname=cellValues[3];
							parent_id=cellValues[4];
							stud_id=cellValues[5];
							
							if(cellValues[6].equalsIgnoreCase("Male"))
								stud_gender="M";
							else
								stud_gender="F";
							
							stud_dob=cellDateValue;
							stud_home_no=(long)cellValues1[1];
							stud_mobile_no=(long)cellValues1[2];
							
							System.out.println("cellvalues:"+stud_roll_no+" "+stud_address+" "+stud_email_id+" "+stud_fname+" "+stud_home_no+" "+stud_lname+" "+stud_mobile_no+" "+parent_id+" "+stud_id+" "+stud_dob+" "+stud_gender);
							
							boolean result = addStudentEntry(stud_id,stud_roll_no,stud_fname,stud_lname,stud_email_id,stud_address,stud_home_no,stud_mobile_no,stud_gender,stud_dob,sectionId,classId,parent_id);
							
							if(!result)
								flag1++;
		
			}
			
			if(flag==0 && flag1==0)
			{	
				request.setAttribute("dataImported","Student List imported successfully");
				/*RequestDispatcher rd = request.getRequestDispatcher("views/teacher/studentSettings.jsp");
				rd.forward(request, response);*/
				response.sendRedirect("views/teacher/studentSettings.jsp");
			}
			else
			{
				request.setAttribute("dataNotImported","One or more records not imported successfully");
				response.sendRedirect("views/teacher/studentSettings.jsp");
			}
			
			file2.close();
			System.out.println("Students data extracted from excel");
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		
	}

	protected boolean addParentEntry(String parent_id,String parent_name,String parent_email_id,Long parent_mobile_no,Long parent_home_no,String parent_job,Long parent_office_no,String parent_gender ) {
		
		AddParentService parentService = new AddParentService();
	
		Parent newParent = new Parent();
		boolean res = false;
		boolean result = false;
	        
	       
	                        newParent = new Parent(parent_id,parent_name,parent_email_id,parent_mobile_no,parent_home_no,parent_job,parent_mobile_no,parent_gender);
	                       
	                        if(! parentService.ifAlreadyExists(newParent)) {
	                        	result = parentService.addParent(newParent);
	                        }
	                        else {
	                        	System.out.println("Parent already exists");
	                        }

	
	  return result;
	}
	
	protected boolean addStudentEntry(String stud_id,int stud_roll_no,String stud_fname,String stud_lname,String stud_email_id,String stud_address,Long stud_home_no,Long stud_mobile_no,String stud_gender,Date stud_dob,String section_id,String class_id,String parent_id){
		AddStudentService studentService = new AddStudentService();
	
		Student newStudent = new Student();
		boolean res = false;
		boolean result = false;
	
		java.sql.Date date=null;
		date=new java.sql.Date(stud_dob.getTime());
	       
		 					Parent parent=new Parent(parent_id);
	                        newStudent = new Student(stud_id,stud_roll_no,stud_fname,stud_lname,stud_email_id,stud_address,stud_home_no,stud_mobile_no,stud_gender,date,section_id,class_id);
	                        newStudent.setParent(parent);
	                        if(!studentService.ifAlreadyExists(newStudent)) {
	                        	result = studentService.addStudent(newStudent);
	                        }
	                        else {
	                        	System.out.println("Student already exists");
	                        }
	       return result;  
	}
	
	@Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

}
